﻿using System;
using System.Collections.Generic;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for FileUploadTable
    /// </summary>
    public static class FileUploadTable
    {
        public static int SaveFileUpload(String sxOriginFilename, String sxPhysicalFilename, 
            String sxFiletype, int iUploadTypeId)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_ORIGINAL_FILENAME";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxOriginFilename;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_PHYSICAL_FILENAME";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxPhysicalFilename;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_FILETYPE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxFiletype;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_UPLOAD_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_FILE_UPLOAD_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iUploadTypeId;

            String sxQuery = "PR_INSERT_T_FILE_UPLOAD";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            
            return Convert.ToInt32(lsParams[0].Value);
        }

        public static String GetUploadPath(int iUploadTypeId)
        {
            String sxOutput = String.Empty;

            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iUploadTypeId;

            String sxQuery = "select PATH from P_FILE_UPLOAD_TYPE where P_FILE_UPLOAD_TYPE_ID = :prmID";
            sxOutput = Convert.ToString(dbl.GetSingleValue(sxQuery, prmID));
            
            return sxOutput;
        }

        public static Object[] GetDownloadURL(int iFileUploadId)
        {
            String sxOutput = String.Empty;

            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iFileUploadId;

            String sxQuery =
                @"
                select 
                    t1.ORIGINAL_FILENAME, 
                    t1.PHYSICAL_FILENAME,
                    p1.PATH
                from
                    T_FILE_UPLOAD t1 
                    left join P_FILE_UPLOAD_TYPE p1
                        on t1.P_FILE_UPLOAD_TYPE_ID = p1.P_FILE_UPLOAD_TYPE_ID
                where 
                    t1.T_FILE_UPLOAD_ID = :prmID
                    and nvl(t1.IS_DELETED, 'N') <> 'Y' ";
            object[] objReturn = dbl.GetMultipleValues(sxQuery, prmID);
            
            return objReturn;
        }

        public static void DeleteFile(int iFileUploadId)
        {
            String sxOutput = String.Empty;

            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iFileUploadId;
            
            String sxQuery =
                @"
                update
                    T_FILE_UPLOAD 
                set
                    IS_DELETED = 'Y'
                where 
                    T_FILE_UPLOAD_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID);
            

        }
        public static void PermanentDeleteFile(int iFileUploadId)
        {
            String sxOutput = String.Empty;

            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iFileUploadId;

            String sxQuery =
                @"
                delete
                from
                    T_FILE_UPLOAD 
                where 
                    T_FILE_UPLOAD_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID);
            

        }
        public static String GetFileNameById(int iFileUploadId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iFileUploadId;

            String sxQuery =
                @" Select ORIGINAL_FILENAME
                    from T_FILE_UPLOAD
                    where T_FILE_UPLOAD_ID = :prmID";
            String sxReturn = dbl.GetSingleValue(sxQuery, prmID).ToString();
            
            return sxReturn;
        }
    }
}